package com.cocostudio.erpapp.db;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.cocostudio.erpapp.common.Debug;

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBadapter";
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "cmef2013";

    private static String CREATE_TABLE_SCHEDULES = "create table " + Tables.SCHEDULE
            + " (_id integer primary key autoincrement, sid integer, sname text, stimespan text, slocation text, category text, date text, eid text, saved integer);";

    private static String CREATE_TABLE_SPEECHS = "create table "
            + Tables.SPEECH
            + " (_id integer primary key autoincrement, ssid integer, name text, timespan text, room text, description text, spid integer, speaker text, spktitle text, sid integer, eid text, date text, saved integer);";

    private static String CREATE_TABLE_EXHIBITORS = "create table "
            + Tables.EXHIBITOR
            + " (_id integer primary key autoincrement, id integer, company text, logo text, boothno text, number text, introduction text, contact text, tel text, email text, categoryid integer, categoryname text, subctgid integer, subctgname text, saved integer);";

    private static String CREATE_TABLE_EX_CATEGORY = "create table " + Tables.EX_CATEGORY + " (_id integer primary key autoincrement, id integer, name text);";

    private static String CREATE_TABLE_EX_SUBCATEGORY = "create table " + Tables.EX_SUBCATEGORY + " (_id integer primary key autoincrement, id integer, name text, ctgid integer);";

    private static String CREATE_TABLE_PRODUCTS = "create table " + Tables.PRODUCT
            + " (_id integer primary key autoincrement, id integer, name text, pic text, description text, category text, exid integer, eid text, act text, saved integer);";

    private static String CREATE_TABLE_BOOTHS = "create table " + Tables.BOOTH
            + " (_id integer primary key autoincrement, id integer, name text, pic text, description text, exid integer, exX integer, exY integer, lastUpdateTime text);";

    private static String CREATE_TABLE_NEWS = "create table " + Tables.NEWS
            + " (_id integer primary key autoincrement, id integer, name text, date text, summary text, source text, pic text, content text);";

    private static String CREATE_TABLE_SPEAKER = "create table " + Tables.SPEAKER
            + " (_id integer primary key autoincrement, spid integer, speaker text, spktitle text, company text, description text);";

    private Context mContext;

    private DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.beginTransaction();
        try {
            createTables(db);
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            if (Debug.LOGE) {
                Log.e(TAG, "onCreate. ", e);
            }
        } finally {
            db.endTransaction();
        }
    }

    private static void createTables(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_SCHEDULES);
        db.execSQL(CREATE_TABLE_SPEECHS);
        db.execSQL(CREATE_TABLE_EXHIBITORS);
        db.execSQL(CREATE_TABLE_EX_CATEGORY);
        db.execSQL(CREATE_TABLE_EX_SUBCATEGORY);
        db.execSQL(CREATE_TABLE_PRODUCTS);
        db.execSQL(CREATE_TABLE_BOOTHS);
        db.execSQL(CREATE_TABLE_NEWS);
        db.execSQL(CREATE_TABLE_SPEAKER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            dropTables(db);
            onCreate(db);
        } catch (SQLException e) {
            if (Debug.LOGE) {
                Log.e(TAG, "onUpgrade. ", e);
            }
        }
    }

    private static void dropTables(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + Tables.SCHEDULE);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.SPEECH);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.EXHIBITOR);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.EX_CATEGORY);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.EX_SUBCATEGORY);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.PRODUCT);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.BOOTH);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.NEWS);
        db.execSQL("DROP TABLE IF EXISTS " + Tables.SPEAKER);
    }

    private static SQLiteDatabase db;

    public static SQLiteDatabase openDatabase(Context context, boolean writable) {
        if (db == null) {
            if (writable) {
                db = new DBHelper(context).getWritableDatabase();
            } else {
                db = new DBHelper(context).getReadableDatabase();
            }
        } else {
            if (db.isReadOnly() && writable) {
                db.close();
                db = new DBHelper(context).getWritableDatabase();
            }
        }
        return db;
    }

    public static void clearDB(Context context) {
        SQLiteDatabase db = openDatabase(context, true);
        dropTables(db);
        createTables(db);
    }

    public static void closeDB() {
        if (db != null) {
            db.close();
            db = null;
        }
    }

}
